/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package DigestModels;

/**
 *
 * @author Prophet,Tony,Sheamus 
 * unzip the mysql-connector-java-3.1.11-bin.jar
 * file. Place the com folder (and org) folder in same directory as this file
 */
import java.io.FileWriter;
import java.sql.*;
import java.util.ArrayList;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.table.DefaultTableModel;

/**
 *
 * @author Prophet,Tony,Sheamus
 */
public class Database {

    java.sql.Connection conn;

    /**
     * Default constructor
     */
    public Database() {
    }

    /**
     * Initialises connection to the database
     *
     * @return represents successful or unsuccessful connection
     */
    public boolean initComponents() {
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = java.sql.DriverManager.getConnection(
                    "jdbc:mysql://localhost/digest?user=root&password=");

        } catch (Exception e) {
            System.out.println(e);
            return false;
        }
        return true;
    }

    /**
     * This method retrieves the names of the items based on type for the
     * database.
     *
     * @param itemType represents the type of item wanted to be retrieved.
     * @return a list of all the names of the items with a particular type.
     */
    public ArrayList getItemList(String itemType) {
        String query = "";
        ArrayList<String> items = new ArrayList<String>();
        try {
            query = "SELECT name FROM meals WHERE type='" + itemType + "'";
            Statement stmt = conn.createStatement();
            ResultSet result = stmt.executeQuery(query);
            while (result.next()) {
                String s = result.getString(1);

                items.add(s);
            }
        } catch (SQLException e) {
            System.out.println("Error");
        }
        return items;
    }

    /**
     * This method retrieve information on a certain item from the database.
     *
     * @param item represents the item be requested.
     * @return a list of information of the requested item
     */
    public ArrayList getItem(String item) {
        String query = "";
        ArrayList items = new ArrayList();
        try {
            query = "SELECT name,price FROM meals WHERE name='" + item + "'";
            Statement stmt = conn.createStatement();
            ResultSet result = stmt.executeQuery(query);
            while (result.next()) {
                String s = result.getString(1);
                System.out.println(s);
                items.add(s);
                items.add(result.getDouble(2));
                items.add(1);
            }
        } catch (SQLException e) {
            System.out.println("Error");
        }
        return items;
    }

    /**
     * This method validates user credentials.
     *
     * @param name represents username.
     * @param password represents users password.
     * @return represent correct or incorrect credentials.
     */
    public boolean checkPassword(String name, String password) {
        ResultSet set = null;
        try {
            Statement statement = conn.createStatement();
            set = statement.executeQuery("select * from employees");
            while (set.next() != false) {
                if (set.getString(2).equals(name) && set.getInt(4) == Integer.parseInt(password)) {
                    return true;
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    /**
     * A method that returns the id of the logged in user
     * @param String
     * @return int
     */

    /**
     *
     * @param name
     * @return
     */    
    public int getCurrWorkerId(String name) {
        String query = "";
        int id = 0;
        try {
            query = "SELECT employee_id FROM employees WHERE name='" + name + "'";
            System.out.println("ha");
            Statement stmt = conn.createStatement();
            System.out.println("haha");
            ResultSet result = stmt.executeQuery(query);
            System.out.println("hahahah");
            while (result.next()) {
                id = result.getInt(1);
                System.out.println(id);
            }
            System.out.println("The computed id is: " + id);
        } catch (SQLException e) {
            System.out.println("Error");
        }
        return id;
    }

    /**
     * A method that does a simple add student to the database
     *
     * @param name
     * @param price
     * @param quantity
     * @param type type of food
     * @return boolean
     */
    public boolean addFood(String name, double price, int quantity, String type) {
        String query = "";
        try {
            query = ("INSERT INTO meals(name, price,quantity,type)"
                    + "VALUES('" + name + "',  '" + price + "', '" + quantity + "', '" + type + "')");
            Statement stmt = conn.createStatement();
            System.out.println("All Fields Correct");
            stmt.executeUpdate(query);
            System.out.println("Sucessfully Executed Query");
            return true;
        } catch (SQLException e) {
            return false;
        }
    }

    /**
     * This method adds individual purchases to the database.
     *
     * @param dtm represents the table holding the data
     * @param cost represent the total cost for items bought.
     * @param amountPaid represents the amount paid by the customer.
     * @param change represents the change that the customers requires.
     * @param employeeId represents the id of the user.
     */
    public void addPurchases(DefaultTableModel dtm, double cost, double amountPaid, double change, int employeeId) {
        int index = addTransaction(cost, amountPaid, change, employeeId);
        String query = "";
        try {
            for (int i = 0; i < dtm.getRowCount(); i++) {
                String food = (String) dtm.getValueAt(i, 0);
                int quantity = (Integer) dtm.getValueAt(i, 2);
                for (int j = 0; j < quantity; j++) {
                    query = ("INSERT INTO purchases(transaction_id, name)VALUES"
                            + "(" + index + ",'" + food + "')");
                    Statement stmt = conn.createStatement();
                    stmt.executeUpdate(query);
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * This method add transactions made by user to the database.
     *
     * @param cost represent the total cost for items bought.
     * @param amountPaid represents the amount paid by the customer.
     * @param change represents the change that the customers requires.
     * @param employeeId represents the id of the user.
     * @return the transaction id.
     */
    public int addTransaction(double cost, double amountPaid, double change, int employeeId) {
        System.out.println("id in the addTransaction: " + employeeId);
        String query = "";
        int index = 0;
        try {
            query = ("INSERT INTO Transactions(amount_paid,cost,`change`,employee_Id,`date`, `time`)VALUES"
                    + "(" + amountPaid + "," + cost + "," + change + "," + employeeId + ", CURDATE(),CURTIME())");
            Statement stmt = conn.createStatement();
            stmt.executeUpdate(query);
            query = ("SELECT transaction_id FROM transactions ORDER BY transaction_id DESC LIMIT 1");
            ResultSet result = stmt.executeQuery(query);
            result.next();
            index = result.getInt(1);

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return index;
    }

    /**
     * This method retrieves the daily summary for a given day.
     *
     * @param day represents the day that the daily summary should be retrieved
     * @return String
     */
    public String getDaySummay(String day) {
        String query = "";
        String summary = "";
        try {
            query = ("SELECT name, count(*) as summary FROM purchases INNER JOIN"
                    + " transactions ON transactions.transaction_Id = purchases.transaction_Id"
                    + " where date = '" + day + "' GROUP BY purchases.name");
            Statement stmt = conn.createStatement();
            ResultSet result = stmt.executeQuery(query);
            System.out.println(day);
            while (result.next()) {
                String a = result.getString(1);
                String b = result.getInt(2) + "";
                summary = summary + "The number of " + a + " sold was " + b + ".\n";
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return summary;
    }

    /*
     * A method that returns the days transaction into a file called digest.csv
     */    
    public void export() {
        String filename = "digest.csv";
        try {
            FileWriter fw = new FileWriter(filename);
            String query = "select * from transactions";
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            while (rs.next()) {
                fw.append(rs.getString(1));
                fw.append(',');
                fw.append(rs.getString(2));
                fw.append(',');
                fw.append(rs.getString(3));
                fw.append(',');
                fw.append(rs.getString(4));
                fw.append(',');
                fw.append(rs.getString(5));
                fw.append('\n');
            }
            fw.flush();
            fw.close();
            System.out.println("CSV File is created successfully.");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    /**
     *
     * @param s
     * @return
     */
    public ArrayList suggestSearch(String s){
        Vector allRows = new Vector();
        Vector row;
        ArrayList<String> search = new ArrayList<>();
        ArrayList rslt = new ArrayList();
        
        String query="";
        
        try {
            
            query = "SELECT purchases.name, transactions.amount_paid, transactions.date, transactions.time, transactions.cost FROM meals, purchases, transactions WHERE purchases.name = meals.name AND purchases.transaction_Id = transactions.transaction_Id AND purchases.name LIKE'%" + s + "%' ORDER BY Transactions.date DESC";
            Statement stmt = conn.createStatement();
            ResultSet result = stmt.executeQuery(query);
            while (result.next()){
                row = new Vector();
                String name = result.getString("name");
                if(!search.contains(name))
                    search.add(name);
                row.add(result.getString("amount_paid"));
                row.add(result.getString("date"));
                row.add(result.getString("time"));
                row.add(result.getString("cost"));
                
                allRows.add(row);
            }
            //values.add(search);
            //values.add(columns);
            rslt.add(search);
            rslt.add(allRows);
                //Collections.sort(suggestions);
                        
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rslt;
    }
      
    /**
     * A method that returns a vector of vals
     * @return Vector
     */
    public Vector getTransactionInfo(){
        String query = "";
        Vector<Vector> vector = new Vector<>();
        Vector<Object> items;
        try{
            query = "SELECT purchases.name, transactions.amount_paid, transactions.date, transactions.time, transactions.cost FROM meals, purchases, transactions WHERE purchases.name = meals.name AND purchases.transaction_Id = transactions.transaction_Id ORDER BY Transactions.date DESC";
            Statement stmt = conn.createStatement();
            ResultSet rslt = stmt.executeQuery(query);
            while(rslt.next()){
                items = new Vector<>();
                items.add(rslt.getString(1));
                items.add(rslt.getDouble(2));
                items.add(rslt.getDate(3));
                items.add(rslt.getTime(4));
                items.add(rslt.getDouble(5));
                vector.add(items);
            }
 
        }catch(SQLException e){
        return vector;
        }
        return vector;
    }
}//class
